﻿using System.Data;
using System.Linq;
using Gumini.JulaDB.Provider;
using Gumini.JulaDB.Test.Common;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace Gumini.JulaDB.Test
{
    /// <summary>
    /// A set of test for more complex queries.
    /// </summary>
    [TestClass]
    public class ComplexQueryTest
    {
        private static JulaDbConnection _connection;
        private static CustomerOrderTestDataCreator _customerData;

        [TestInitialize]
        public void InitializeTest()
        {
            // database is initialized before every test to discard any changes made by previous tests
            _connection = TestUtils.CreateTestDatabase();
            _customerData = new CustomerOrderTestDataCreator(_connection);
        }

        [TestCleanup]
        public void CleanupTest()
        {
            _connection.Close();
        }

        private static void CheckValuesInColumn(DataTable table, string columnName, object value, int expectedCount)
        {
            var column = table.Columns[columnName];
            var matchingRows = table.AsEnumerable().Where(r => object.Equals(r[column], value)).Count();
            Assert.AreEqual(expectedCount, matchingRows);
        }

        /// <summary>
        /// A join on 4 different tables.
        /// </summary>
        [TestMethod]
        public void SelectAllTables()
        {
            var result = _connection.ExecuteValidSelect(
                @"select *
                  from Customer c, Order o, OrderItem oi, Product p
                  where c.CustomerId = o.CustomerId
                    and o.OrderId = oi.OrderId
                    and oi.ProductId = p.ProductId");
            Assert.AreEqual(6, result.Rows.Count);
            CheckValuesInColumn(result, "Name", "John", 2);
            CheckValuesInColumn(result, "Name", "Mary", 3);
            CheckValuesInColumn(result, "Name", "Jonathan", 0);
            CheckValuesInColumn(result, "Name", "Adam", 0);
            CheckValuesInColumn(result, "Name", "Ann", 0);
            CheckValuesInColumn(result, "Name", "Tom", 1);
        }
        
        /// <summary>
        /// A join on 4 different tables with GROUP BY, ORDER BY and a scalar function.
        /// </summary>
        [TestMethod]
        public void SelectGroupByOrderBy()
        {
            var result = _connection.ExecuteValidSelect(
                @"select c.CustomerId, sum(p.Price)
                  from Customer c, Order o, OrderItem oi, Product p
                  where c.CustomerId = o.CustomerId
                    and o.OrderId = oi.OrderId
                    and oi.ProductId = p.ProductId
                  group by c.CustomerId
                  order by c.CustomerId");
            Assert.AreEqual(3, result.Rows.Count);
        }

        /// <summary>
        /// A join on 4 different tables with a WHERE condition, GROUP BY, ORDER BY,
        /// scalar function and output column aliases.
        /// </summary>
        [TestMethod]
        public void SelectGroupByOrderBy2()
        {
            var result = _connection.ExecuteValidSelect(
                @"select distinct c.Name as CustomerName, sum(p.Price) as TotalAmount
                  from Customer c, Order o, OrderItem oi, Product p
                  where p.price > 10
                    and c.CustomerId = o.CustomerId
                    and o.OrderId = oi.OrderId
                    and oi.ProductId = p.ProductId
                  group by c.CustomerId, c.Name
                  order by c.Name desc, c.CustomerId");
            Assert.AreEqual(2, result.Rows.Count);
        }
    }
}
